<?php

/**
 * Class that operate on table 'library'. Database Sqlite.
 *
 * @author: Dragan Milchevski
 * @date: 2012-06-23 13:23
 */
class DocumentSqliteDAO implements DocumentDAO {

    /**
     * Get Domain object by primry key
     *
     * @param String $id primary key
     * @return LibrarySqlite 
     */
    public function load($id) {
        $sql = 'SELECT * FROM library WHERE id = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    /**
     * Get all records from table
     */
    public function queryAll() {
        $sql = 'SELECT * FROM library';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    /**
     * Get all records from table ordered by field
     *
     * @param $orderColumn column name
     */
    public function queryAllOrderBy($orderColumn) {
        $sql = 'SELECT * FROM library ORDER BY ' . $orderColumn;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }
    /**
     * Get all journals from table ordered by secondary_title
     *
     */
    public function queryAllJournalsOrdered() {
    	$sql = 'SELECT DISTINCT journal FROM library ORDER BY journal ASC';
    	$sqlQuery = new SqlQuery($sql);
    	return $this->getList($sqlQuery);
    }

    /**
     * Get all secondary_title from table ordered by secondary_title
     *
     */
    public function queryAllSecTitleOrdered() {
    	$sql = 'SELECT DISTINCT secondary_title FROM library ORDER BY secondary_title ASC';
    	$sqlQuery = new SqlQuery($sql);
    	return $this->getList($sqlQuery);
    }
    
    /**
     * Delete record from table
     * @param library primary key
     */
    public function delete($id) {
        $sql = 'DELETE FROM library WHERE id = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->executeUpdate($sqlQuery);
    }

    /**
     * Insert record to table
     *
     * @param LibrarySqlite library
     */
    public function insert($document) {
    	
        if(!isset($document)) return null;
        $sql = 'INSERT INTO library (file, authors, affiliation, title, journal, secondary_title, year, volume, issue, pages, abstract, keywords, editor, publisher, place_published, reference_type, uid, doi, url, addition_date, rating, authors_ascii, title_ascii, abstract_ascii, added_by, modified_by, modified_date, custom1, custom2, custom3, custom4) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
       
        
       // $sql = 'DELETE FROM library WHERE id = ?';
        $sqlQuery = new SqlQuery($sql);
        
       
        $sqlQuery->set($document->getFile()->getNewName());
        $sqlQuery->set($document->getMetadata()->getAuthors());
        $sqlQuery->set($document->getMetadata()->getAffiliation());
        $sqlQuery->set($document->getMetadata()->getTitle());
        $sqlQuery->set($document->getMetadata()->getJournal());
        $sqlQuery->set($document->getMetadata()->getSecondaryTitle());
        $sqlQuery->set($document->getMetadata()->getYear());
        $sqlQuery->set($document->getMetadata()->getVolume());
        $sqlQuery->set($document->getMetadata()->getIssue());
        $sqlQuery->set($document->getMetadata()->getPages());
        $sqlQuery->set($document->getMetadata()->getAbstract());
        $keywords_array = $document->getMetadata()->getKeywords();
        $keywords_array2="";
        $i=0;
        foreach($keywords_array as $keyword){
        	$keywords_array2[$i]=$keyword->getKeyword();
        	$i++;
        }
        
        $keywords_string = implode(' / ', $keywords_array2);
       // $var= $document->getMetadata()->;
      
       // $keywords_string = "";
       
        
        $sqlQuery->set($keywords_string);
       
        $sqlQuery->set($document->getMetadata()->getEditor());
        $sqlQuery->set($document->getMetadata()->getPublisher());
        $sqlQuery->set($document->getMetadata()->getPlacePublished());
        $sqlQuery->set($document->getMetadata()->getReferenceType());
        $sqlQuery->set($document->getUid());
        $sqlQuery->set($document->getMetadata()->getDoi());
        $sqlQuery->set($document->getMetadata()->getUrl());
        $sqlQuery->set($document->getAdditionDate());
        
        $sqlQuery->setNumber($document->getMetadata()->getRating());
        $sqlQuery->set($document->getMetadata()->getAutorsAscii());
        $sqlQuery->set($document->getMetadata()->getTitleAscii());
        $sqlQuery->set($document->getMetadata()->getAbstractAscii());
        $added = $document->getAddedBy()->getId();
        $modified = $document->getModifiedBy()->getId();
        
        if(isset($added) && $added!=0){
        	$sqlQuery->setNumber($added);
        }else{
        	$sqlQuery->set('');
        }
        if(isset($modified) && $modified != 0)
        	$sqlQuery->setNumber($modified);
        else
        	$sqlQuery->set('');
        $sqlQuery->set($document->getModifiedDate());
        $sqlQuery->set($document->getMetadata()->getCustom1());
        $sqlQuery->set($document->getMetadata()->getCustom2());
        $sqlQuery->set($document->getMetadata()->getCustom3());
        $sqlQuery->set($document->getMetadata()->getCustom4());
        
        $id = $this->executeInsert($sqlQuery);
      
        //$document->setId($id);
       // $document->getMetadata()->getFile()->setNewName($this->lastFile($id));
        return $id;
    }
    
    

    /**
     * insert record in table shelves
     *
     * @param LibrarySqlite library
     */
    public function insertShelf($document_id,$user_id) {
    	if(!isset($document_id) && !isset($user_ID)) return null;
    	$sql = 'INSERT OR IGNORE INTO shelves (fileID,userID) VALUES (?,?)';
    	$sqlQuery = new SqlQuery($sql);
    	$sqlQuery->setNumber($document_id);
    	$sqlQuery->setNumber($user_id);
    	$id = $this->executeInsert($sqlQuery);
		return $id;
    }

    /**
     * insert record in table full_text from fultext database
     *
     * @param LibrarySqlite library
     */
    
    public function insertFullText($id,$full_text){
    
    	if(!isset($id) && !isset($full_text)) return null;
    	
    	//ConnectionProperty::$database_name = 'fulltext';
    	$sql = 'INSERT INTO full_text (fileID, full_text) VALUES (?,?)';
    	$sqlQuery = new SqlQuery($sql);
    	$sqlQuery->setNumber($id);
    	$sqlQuery->set($full_text);
    	$id = $this->executeInsert($sqlQuery);
    	
    	//ConnectionProperty::$database_name = 'library';
    	return $id;
    }
    
    /**
     * delete record in table full_text from fultext database
     *
     * @param LibrarySqlite library
     */
    
    public function deleteFullText($id){
    	
    	if(!isset($id)) return null;
    	//ConnectionProperty::$database_name = 'fulltext';
    	$sql = 'DELETE FROM full_text WHERE fileID=?';
    	$sqlQuery = new SqlQuery($sql);
    	$sqlQuery->setNumber($id);
    	$id1 = $this->executeUpdate($sqlQuery);
    	//ConnectionProperty::$database_name = 'library';
    	return $id1;
    }
    
    
    
    
    /**
     * Update record in table
     *
     * @param LibrarySqlite library
     */
    public function update($library) {
        $sql = 'UPDATE library SET file = ?, authors = ?, affiliation = ?, title = ?, journal = ?, secondary_title = ?, year = ?, volume = ?, issue = ?, pages = ?, abstract = ?, keywords = ?, editor = ?, publisher = ?, place_published = ?, reference_type = ?, uid = ?, doi = ?, url = ?, addition_date = ?, rating = ?, authors_ascii = ?, title_ascii = ?, abstract_ascii = ?, added_by = ?, modified_by = ?, modified_date = ?, custom1 = ?, custom2 = ?, custom3 = ?, custom4 = ? WHERE id = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($library->file);
        $sqlQuery->set($library->authors);
        $sqlQuery->set($library->affiliation);
        $sqlQuery->set($library->title);
        $sqlQuery->set($library->journal);
        $sqlQuery->set($library->secondaryTitle);
        $sqlQuery->set($library->year);
        $sqlQuery->set($library->volume);
        $sqlQuery->set($library->issue);
        $sqlQuery->set($library->pages);
        $sqlQuery->set($library->abstract);
        $sqlQuery->set($library->keywords);
        $sqlQuery->set($library->editor);
        $sqlQuery->set($library->publisher);
        $sqlQuery->set($library->placePublished);
        $sqlQuery->set($library->referenceType);
        $sqlQuery->set($library->uid);
        $sqlQuery->set($library->doi);
        $sqlQuery->set($library->url);
        $sqlQuery->set($library->additionDate);
        $sqlQuery->setNumber($library->rating);
        $sqlQuery->set($library->authorsAscii);
        $sqlQuery->set($library->titleAscii);
        $sqlQuery->set($library->abstractAscii);
        $sqlQuery->setNumber($library->addedBy);
        $sqlQuery->setNumber($library->modifiedBy);
        $sqlQuery->set($library->modifiedDate);
        $sqlQuery->set($library->custom1);
        $sqlQuery->set($library->custom2);
        $sqlQuery->set($library->custom3);
        $sqlQuery->set($library->custom4);

        $sqlQuery->setNumber($library->id);
        return $this->executeUpdate($sqlQuery);
    }

    /**
     * Delete all rows
     */
    public function clean() {
        $sql = 'DELETE FROM library';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryByFile($value) {
        $sql = 'SELECT * FROM library WHERE file = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByAuthors($value) {
        $sql = 'SELECT * FROM library WHERE authors = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByAffiliation($value) {
        $sql = 'SELECT * FROM library WHERE affiliation = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByTitle($value) {
        $sql = 'SELECT * FROM library WHERE title = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }
    
    public function queryByTitleRestrictLength ($value){
    	$sql = 'SELECT id,title FROM library WHERE title LIKE ? AND length(title) <= ? +2 LIMIT 1';
    	$sqlQuery = new SqlQuery($sql);
    	$title_query=substr($value,0,-2)."%";
    	$title_length=strlen($value);
    	$sqlQuery->set($title_query);
    	$sqlQuery->setNumber($title_length);
    	return $this->getList($sqlQuery);
    }
        

    public function queryByJournal($value) {
        $sql = 'SELECT * FROM library WHERE journal = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryBySecondaryTitle($value) {
        $sql = 'SELECT * FROM library WHERE secondary_title = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByYear($value) {
        $sql = 'SELECT * FROM library WHERE year = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByVolume($value) {
        $sql = 'SELECT * FROM library WHERE volume = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByIssue($value) {
        $sql = 'SELECT * FROM library WHERE issue = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByPages($value) {
        $sql = 'SELECT * FROM library WHERE pages = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByAbstract($value) {
        $sql = 'SELECT * FROM library WHERE abstract = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByKeywords($value) {
        $sql = 'SELECT * FROM library WHERE keywords = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByEditor($value) {
        $sql = 'SELECT * FROM library WHERE editor = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByPublisher($value) {
        $sql = 'SELECT * FROM library WHERE publisher = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByPlacePublished($value) {
        $sql = 'SELECT * FROM library WHERE place_published = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByReferenceType($value) {
        $sql = 'SELECT * FROM library WHERE reference_type = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByUid($value) {
        $sql = 'SELECT * FROM library WHERE uid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByDoi($value) {
        $sql = 'SELECT * FROM library WHERE doi = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByUrl($value) {
        $sql = 'SELECT * FROM library WHERE url = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByAdditionDate($value) {
        $sql = 'SELECT * FROM library WHERE addition_date = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByRating($value) {
        $sql = 'SELECT * FROM library WHERE rating = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByAuthorsAscii($value) {
        $sql = 'SELECT * FROM library WHERE authors_ascii = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByTitleAscii($value) {
        $sql = 'SELECT * FROM library WHERE title_ascii = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByAbstractAscii($value) {
        $sql = 'SELECT * FROM library WHERE abstract_ascii = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByAddedBy($value) {
        $sql = 'SELECT * FROM library WHERE added_by = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByModifiedBy($value) {
        $sql = 'SELECT * FROM library WHERE modified_by = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByModifiedDate($value) {
        $sql = 'SELECT * FROM library WHERE modified_date = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByCustom1($value) {
        $sql = 'SELECT * FROM library WHERE custom1 = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByCustom2($value) {
        $sql = 'SELECT * FROM library WHERE custom2 = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByCustom3($value) {
        $sql = 'SELECT * FROM library WHERE custom3 = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByCustom4($value) {
        $sql = 'SELECT * FROM library WHERE custom4 = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByCategory($value) {
        $sql = 'SELECT a.* FROM library a, filescategories b WHERE a.id = b.fileID AND b.categoryID = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function deleteByFile($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByAuthors($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }
    
    public function getNewFileName() {
    	$sql = 'SELECT IFNULL((SELECT SUBSTR(\'0000\' || CAST(MAX(file)+1 AS TEXT) || \'.pdf\',-9,9) FROM library),\'00001.pdf\') as filename';
    	$sqlQuery = new SqlQuery($sql);
    	return $this->querySingleResult($sqlQuery);
    }

    public function deleteByAffiliation($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByTitle($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByJournal($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteBySecondaryTitle($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByYear($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByVolume($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByIssue($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByPages($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByAbstract($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByKeywords($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByEditor($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByPublisher($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByPlacePublished($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByReferenceType($value) {
        $sql = 'DELETE FROM library WHERE text = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    /**
     * Read row
     *
     * @return LibrarySqlite 
     */
    protected function readRow($row) {
    	
    	//echo "Connection name=".ConnectionProperty::$database_name;
        $document = new Document();
        $metadata = new Metadata();
        $file = new File();

        $document->setId($row['id']);
        $file->setFilename($row['file']);
        $metadata->setAuthors($row['authors']);
        $metadata->setAffiliation($row['affiliation']);
        $metadata->setTitle($row['title']);
        $metadata->setJournal($row['journal']);
        $metadata->setSecondaryTitle($row['secondary_title']);
        $metadata->setYear($row['year']);
        $metadata->setVolume($row['volume']);
        $metadata->setIssue($row['issue']);
        $metadata->setPages($row['pages']);
        $metadata->setAbstract($row['abstract']);
        $metadata->setKeywords($row['keywords']);
        $metadata->setEditor($row['editor']);
        $metadata->setPublisher($row['publisher']);
        $metadata->setPlacePublished($row['place_published']);
        $metadata->setReferenceType($row['reference_type']);
        $document->setUid($row['uid']);
        $metadata->setDoi($row['doi']);
        $metadata->setUrl($row['url']);
        $document->setAdditionDate($row['addition_date']);
        $metadata->setRating($row['rating']);
        $metadata->setAutorsAscii($row['authors_ascii']);
        $metadata->setTitleAscii($row['title_ascii']);
        $metadata->setAbstractAscii($row['abstract_ascii']);
        $document->setModifiedDate($row['modified_date']);
        $metadata->setCustom1($row['custom1']);
        $metadata->setCustom2($row['custom2']);
        $metadata->setCustom3($row['custom3']);
        $metadata->setCustom4($row['custom4']);

        ConnectionProperty::$database_name = 'library';
        if(isset($row['id'])){
	        $document->setCategories(DAOFactory::getCategoriesDAO()->queryByDocument($row['id']));
	        $document->setNotes(DAOFactory::getNotesDAO()->queryByFileID($row['id']));
	        $document->setProjects(DAOFactory::getProjectsDAO()->queryByDocument($row['id']));
        }
        ConnectionProperty::$database_name = 'users';
        if ($row['added_by'] != "")
            $document->setAddedBy(DAOFactory::getUsersDAO()->load($row['added_by'])); //create new user
        if ($row['modified_by'] != "")
            $document->setModifiedBy(DAOFactory::getUsersDAO()->load($row['modified_by'])); //create new user
        ConnectionProperty::$database_name = 'library';
        
        $document->setMetadata($metadata);
        $document->setFile($file);
        
        return $document;
    }

    protected function getList($sqlQuery) {
        $tab = QueryExecutor::execute($sqlQuery);
        $ret = array();
        for ($i = 0; $i < count($tab); $i++) {
            $ret[$i] = $this->readRow($tab[$i]);
        }
        return $ret;
    }

    /**
     * Get row
     *
     * @return LibrarySqlite 
     */
    protected function getRow($sqlQuery) {
        $tab = QueryExecutor::execute($sqlQuery);
        if (count($tab) == 0) {
            return null;
        }
        return $this->readRow($tab[0]);
    }

    /**
     * Execute sql query
     */
    protected function execute($sqlQuery) {
        return QueryExecutor::execute($sqlQuery);
    }

    /**
     * Execute sql query
     */
    protected function executeUpdate($sqlQuery) {
        return QueryExecutor::executeUpdate($sqlQuery);
    }

    /**
     * Query for one row and one column
     */
    protected function querySingleResult($sqlQuery) {
        return QueryExecutor::queryForString($sqlQuery);
    }

    /**
     * Insert row to table
     */
    protected function executeInsert($sqlQuery) {
    	
        return QueryExecutor::executeInsert($sqlQuery);
    }

    /**
     * Creates an empty Model
     */
    public function create() {
        return new Document();
    }

    /**
     *
     * @param type $id
     * @return type 
     */
    public function getlastFilename($id){
        $sql = 'SELECT max(file) FROM library';
        if(isset($id)) $sql = "SELECT file FROM library WHERE id = ?";
        $sqlQuery = new SqlQuery($sql);        
        $sqlQuery->setNumber($id);
        return $this->querySingleResult($sqlQuery);               
    }

    /**
     * General query with 1 to n paratemeters (including the logic 
     * condition AND, OR per attribute)
     * @author Lisette
     * @param type $parameters
     * @return type 
     */
    public function query($parameters) {
        $sql = 'SELECT * FROM library WHERE ';

        if (isset($parameters['file']))
            $sql .= " file = ? " . $parameters['file']['logic'];
        if (isset($parameters['authors']))
            $sql .= " authors = ? " . $parameters['author']['logic'];
        if (isset($parameters['affiliation']))
            $sql .= " affiliation = ? " . $parameters['affiliation']['logic'];
        if (isset($parameters['title']))
            $sql .= " title = ? " . $parameters['title']['logic'];
        if (isset($parameters['journal']))
            $sql .= " journal = ? " . $parameters['journal']['logic'];
        if (isset($parameters['secondary_title']))
            $sql .= " secondary_title = ? " . $parameters['secondary_title']['logic'];
        if (isset($parameters['year']))
            $sql .= " year = ? " . $parameters['year']['logic'];
        if (isset($parameters['volume']))
            $sql .= " volume = ? " . $parameters['volume']['logic'];
        if (isset($parameters['issue']))
            $sql .= " issue = ? " . $parameters['issue']['logic'];
        if (isset($parameters['pages']))
            $sql .= " pages = ? " . $parameters['pages']['logic'];
        if (isset($parameters['abstract']))
            $sql .= " abstract = ? " . $parameters['abstract']['logic'];
        if (isset($parameters['keywords']))
            $sql .= " keywords = ? " . $parameters['keywords']['logic'];
        if (isset($parameters['editor']))
            $sql .= " editor = ? " . $parameters['editor']['logic'];
        if (isset($parameters['publisher']))
            $sql .= " publisher = ? " . $parameters['publisher']['logic'];
        if (isset($parameters['place_published']))
            $sql .= " place_published = ? " . $parameters['place_published']['logic'];
        if (isset($parameters['reference_type']))
            $sql .= " reference_type = ? " . $parameters['reference_type']['logic'];
        if (isset($parameters['uid']))
            $sql .= " uid = ? " . $parameters['uid']['logic'];
        if (isset($parameters['doi']))
            $sql .= " doi = ? " . $parameters['doi']['logic'];
        if (isset($parameters['url']))
            $sql .= " url = ? " . $parameters['url']['logic'];
        if (isset($parameters['addition_date']))
            $sql .= " addition_date = ? " . $parameters['addition_date']['logic'];
        if (isset($parameters['rating']))
            $sql .= " rating = ? " . $parameters['rating']['logic'];
        if (isset($parameters['authors_ascii']))
            $sql .= " authors_ascii = ? " . $parameters['authors_ascii']['logic'];
        if (isset($parameters['title_ascii']))
            $sql .= " title_ascii = ? " . $parameters['title_ascii']['logic'];
        if (isset($parameters['abstract_ascii']))
            $sql .= " abstract_ascii = ? " . $parameters['abstract_ascii']['logic'];
        if (isset($parameters['added_by']))
            $sql .= " added_by = ? " . $parameters['added_by']['logic'];
        if (isset($parameters['modified_by']))
            $sql .= " modified_by = ? " . $parameters['modified_by']['logic'];
        if (isset($parameters['modified_date']))
            $sql .= " modified_date = ? " . $parameters['modified_date']['logic'];
        if (isset($parameters['custom1']))
            $sql .= " custom1 = ? " . $parameters['custom1']['logic'];
        if (isset($parameters['custom2']))
            $sql .= " custom2 = ? " . $parameters['custom2']['logic'];
        if (isset($parameters['custom3']))
            $sql .= " custom3 = ? " . $parameters['custom3']['logic'];
        if (isset($parameters['custom4']))
            $sql .= " custom4 = ? " . $parameters['custom4']['logic'];

        $sqlQuery = new SqlQuery($sql);

        if (isset($parameters['file']))
            $sqlQuery->set($parameters['file']['value']);
        if (isset($parameters['authors']))
            $sqlQuery->set($parameters['author']['value']);
        if (isset($parameters['affiliation']))
            $sqlQuery->set($parameters['affiliation']['value']);
        if (isset($parameters['title']))
            $sqlQuery->set($parameters['title']['value']);
        if (isset($parameters['journal']))
            $sqlQuery->set($parameters['journal']['value']);
        if (isset($parameters['secondary_title']))
            $sqlQuery->set($parameters['secondary_title']['value']);
        if (isset($parameters['year']))
            $sqlQuery->set($parameters['year']['value']);
        if (isset($parameters['volume']))
            $sqlQuery->set($parameters['volume']['value']);
        if (isset($parameters['issue']))
            $sqlQuery->set($parameters['issue']['value']);
        if (isset($parameters['pages']))
            $sqlQuery->set($parameters['pages']['value']);
        if (isset($parameters['abstract']))
            $sqlQuery->set($parameters['abstract']['value']);
        if (isset($parameters['keywords']))
            $sqlQuery->set($parameters['keywords']['value']);
        if (isset($parameters['editor']))
            $sqlQuery->set($parameters['editor']['value']);
        if (isset($parameters['publisher']))
            $sqlQuery->set($parameters['publisher']['value']);
        if (isset($parameters['place_published']))
            $sqlQuery->set($parameters['place_published']['value']);
        if (isset($parameters['reference_type']))
            $sqlQuery->set($parameters['reference_type']['value']);
        if (isset($parameters['uid']))
            $sqlQuery->set($parameters['uid']['value']);
        if (isset($parameters['doi']))
            $sqlQuery->set($parameters['doi']['value']);
        if (isset($parameters['url']))
            $sqlQuery->set($parameters['url']['value']);
        if (isset($parameters['addition_date']))
            $sqlQuery->set($parameters['addition_date']['value']);
        if (isset($parameters['rating']))
            $sqlQuery->setNumber($parameters['rating']['value']);
        if (isset($parameters['authors_ascii']))
            $sqlQuery->set($parameters['authors_ascii']['value']);
        if (isset($parameters['title_ascii']))
            $sqlQuery->set($parameters['title_ascii']['value']);
        if (isset($parameters['abstract_ascii']))
            $sqlQuery->set($parameters['abstract_ascii']['value']);
        if (isset($parameters['added_by']))
            $sqlQuery->setNumber($parameters['added_by']['value']);
        if (isset($parameters['modified_by']))
            $sqlQuery->setNumber($parameters['modified_by']['value']);
        if (isset($parameters['modified_date']))
            $sqlQuery->set($parameters['modified_date']['value']);
        if (isset($parameters['custom1']))
            $sqlQuery->set($parameters['custom1']['value']);
        if (isset($parameters['custom2']))
            $sqlQuery->set($parameters['custom2']['value']);
        if (isset($parameters['custom3']))
            $sqlQuery->set($parameters['custom3']['value']);
        if (isset($parameters['custom4']))
            $sqlQuery->set($parameters['custom4']['value']);

        return $this->getList($sqlQuery);
    }

}

?>